package com.wanshi.dao;

import com.wanshi.entity.Product;
import com.wanshi.exception.UserException;
import com.wanshi.utils.JDBCUtil;
import com.wanshi.utils.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ProdDao {
    public void addProd(Product prod) {
        String sql = "insert into products (id,name,price,typeid,pnum,imgurl,description,state,add_time,update_time,user_id)" +
                " values(null,?,?,?,?,?,?,1,null,null,?)";
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        try {
            runner.update(sql,prod.getName(),prod.getPrice(),prod.getTypeid(),
                    prod.getPnum(),prod.getImgurl(),prod.getDescription(),prod.getUser_id());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new UserException("添加商品失败");
        }
    }

    public List<Product> findProdByUser(int id) {
        String sql = "SELECT *,(SELECT NAME from `type` WHERE id = typeid) `type` FROM products WHERE user_id =? ORDER BY add_time desc";
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        try {
            return runner.query(sql, new BeanListHandler<>(Product.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void updateProdStateById(String id, String state) {
        String sql = "update products set state = ? where id = ?";
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        try {
            runner.update(sql,state,id);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new UserException("修改状态失败");
        }
    }

    public Product findProdById(String id) {
        String sql = "SELECT *,(SELECT NAME from `type` WHERE id = typeid) `type` FROM products WHERE id =?";
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        try {
            return runner.query(sql, new BeanHandler<>(Product.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public void updateProd(Product prod) {
        String sql = "UPDATE products SET NAME = ?,price =?,typeid = ?,pnum =?,update_time=? ";

        List<Object> list = new ArrayList<>();
        list.add(prod.getName());
        list.add(prod.getPrice());
        list.add(prod.getTypeid());
        list.add(prod.getPnum());
        list.add(new Date());

        if(!StringUtil.isEmpty(prod.getImgurl())){
            sql += "imgurl = ?,";
            list.add(prod.getImgurl());
        }
        sql += "description=? WHERE id = ?";
        list.add(prod.getDescription());
        list.add(prod.getId());

        System.out.println(sql);
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        try {
            runner.update(sql,list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new UserException("添加商品失败");
        }
    }




    public List<Product> findByPageAndCond(int i, int size, String name,String typeid) {
//几个不确定, 不确定那个是第一个条件
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select *,(SELECT NAME FROM TYPE WHERE id = products.typeid) type from products where state=1";

        List<Object> objs = new ArrayList<>();
        if(name != null && !name.equals("")){
            sql += " and name like ? ";
            objs.add( "%"+ name+"%");
        }

        if(typeid != null && !typeid.equals("")){
            sql += " and typeid = ?";
            objs.add(typeid);
        }

        sql += " order by add_time desc ";

        sql += " limit ?,?";
        objs.add(i);
        objs.add(size);

        System.out.println(sql);
        try {
            return runner.query(sql,new BeanListHandler<>(Product.class),objs.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Long getCountByCond(String name,String typeid) {
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select count(*) from products where state=1 ";
        List<Object> objs = new ArrayList<>();
        if(name != null && !name.equals("")){
            sql += " and name like ? ";
            objs.add( "%"+ name+"%");
        }

        if(typeid != null && !typeid.equals("")){
            sql += " and typeid = ?";
            objs.add(typeid);
        }

        try {
            return (long)runner.query(sql,new ScalarHandler(),objs.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //根据id减去库存
    public void updateProdNumById(Connection conn,String id, int num) {
        String sql = "update products set pnum = pnum-? where id = ?";
        QueryRunner runner = new QueryRunner();
        try {
            runner.update(conn,sql,num,id);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new UserException("库存不足");
        }
    }

    public void updateProdNumByIdAdd(Connection conn,String id, int num) throws SQLException {
        String sql = "update products set pnum = pnum+? where id = ?";
        QueryRunner runner = new QueryRunner();
            runner.update(conn,sql,num,id);
    }


}
